﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;


namespace BuildForm
{    
    public partial class Orders : Form
    {
        SqlConnection con = new SqlConnection();
        
        

        #region MyCode
        public Orders()
        {
            try
            {
                InitializeComponent();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                LoadOrdersInfo();
                cbSearchBy.SelectedIndex = 0;
                cbShipCountry.SelectedIndex = 0;
                cbCustID.SelectedIndex = - 1;
                
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
                btnSeeDetail.Enabled = false;
                btnReload.Enabled = false;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }

        }

        void LoadOrdersInfo()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM Sales.Orders";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                        dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(),
                        dr[11].ToString(), dr[12].ToString(), dr[13].ToString());
                }
                dr.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Can not load!","Error");
            }
            finally
            {
                con.Close();
            }
        }

        void AddOrders()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "dbo.sp_Orders_Insert";

                SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
                if (cbCustID.SelectedIndex != -1)
                {
                    param.Value = cbCustID.SelectedValue;
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = cbEmpID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                param.Value = dtpOrderDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                param.Value = dtpRequiredDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                if (dtpShippedDate.Checked == true)
                {
                    param.Value = dtpShippedDate.Value.ToShortDateString();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipperid", SqlDbType.Int);
                param.Value = cbShipperID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@freight", SqlDbType.Money);
                param.Value = nudFreight.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
                param.Value = txtShipName.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
                param.Value = txtShipAddress.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
                param.Value = txtShipCity.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
                if (txtShipRegion.Text.Trim() != "")
                {
                    param.Value = txtShipRegion.Text.Trim();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
                if (mtbShipPostalCode.Text.Trim() != "")
                {
                    param.Value = mtbShipPostalCode.Text.Trim();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
                param.Value = cbShipCountry.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                // call detail table
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT MAX(orderid) FROM Sales.Orders";
                SqlDataReader dr = cmd.ExecuteReader();

                dr.Read();
                OrderDetails tblOrderDetails = new OrderDetails(dr[0].ToString(), true);
                tblOrderDetails.Show();
                
            }
            catch (Exception ex)
            {

                throw(ex);
            }
            finally
            {
                con.Close();
            }

        }

        void UpdateOrders()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Orders_Update";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = txtOrderID.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@custid", SqlDbType.Int);
                param.Value = (Int32)cbCustID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = (Int32)cbEmpID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                param.Value = dtpOrderDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                param.Value = dtpRequiredDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                if (dtpShippedDate.Checked == true)
                {
                    param.Value = dtpShippedDate.Value.ToShortDateString();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipperid", SqlDbType.Int);
                param.Value = cbShipperID.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@freight", SqlDbType.Money);
                param.Value = nudFreight.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
                param.Value = txtShipName.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
                param.Value = txtShipAddress.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
                param.Value = txtShipCity.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
                if (txtShipRegion.Text.Trim() != "")
                {
                    param.Value = txtShipRegion.Text.Trim();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
                if (mtbShipPostalCode.Text.Trim() != "")
                {
                    param.Value = mtbShipPostalCode.Text.Trim();
                }
                else
                {
                    param.Value = DBNull.Value;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
                param.Value = cbShipCountry.Text.Trim();
                cmd.Parameters.Add(param);


                cmd.ExecuteNonQuery();
                MessageBox.Show("Update successful!");
            }
            catch (Exception ex)
            {

                throw(ex);
            }
            finally
            {
                con.Close();
            }

        }

        void DeleteOrders()
        {
            try
            {
                // Check if orderDetail is empty
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Orders_Delete";

                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = txtOrderID.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Delete successful!");
            }
            catch (Exception ex)
            {
                throw(ex);
            }
            finally
            {
                con.Close();
            }

        }

        void initData()
        {
            gvOrders.ClearSelection();
            errorProvider1.Clear();
            txtOrderID.Text = "";
            txtShipAddress.Text = "";
            txtShipCity.Text = "";
            cbShipCountry.Text = "";
            txtShipName.Text = "";
            txtShipRegion.Text = "";
            mtbShipPostalCode.Text = "";
            cbShipperID.SelectedIndex = 0;
            cbEmpID.SelectedIndex = 0;
            cbCustID.SelectedIndex = 0;
            dtpOrderDate.Value = DateTime.Now;
            dtpRequiredDate.Value = DateTime.Now;
            dtpShippedDate.Value = DateTime.Now;
            nudFreight.Text = "0.0000";
            tmp();
            
            cbShipCountry.SelectedIndex = 0;
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            btnSeeDetail.Enabled = false;
            btnAdd.Enabled = true;
            btnNew.Enabled = true;
        }

        void SearchOrders()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                if (cbSearchBy.SelectedIndex == 0)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE orderdate = '{0}'", dtpSearch.Value.ToShortDateString());
                }
                else
                {
                    if (cbSearchBy.SelectedIndex == 1)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE requireddate = '{0}'", dtpSearch.Value.ToShortDateString());
                    }
                    if (cbSearchBy.SelectedIndex == 2)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE shippeddate = '{0}'", dtpSearch.Value.ToShortDateString());
                    }
                }
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                        dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(),
                        dr[11].ToString(), dr[12].ToString(), dr[13].ToString());
                }
                dr.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Can not connect to database !","Error");
            }
            finally
            {
                con.Close();
            }
        }

        void getDataSource_CustomerID()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT custid, companyname FROM Sales.Customers";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "Sales.Customers");
            da.Fill(ds, "Sales.Customers");
            DataTable dt = ds.Tables["Sales.Customers"];
            da.Fill(dt);
            cbCustID.DataSource = dt;
            cbCustID.DisplayMember = "companyname";
            cbCustID.ValueMember = "custid";
        }

        void getDataSource_EmployeesID()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT empid, lastname + ' ' + firstname AS fullname FROM HR.Employees";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "HR.Employees");
            da.Fill(ds, "HR.Employees");
            DataTable dt = ds.Tables["HR.Employees"];
            da.Fill(dt);
            cbEmpID.DataSource = dt;
            cbEmpID.DisplayMember = "fullname";
            cbEmpID.ValueMember = "empid";
        }

        void getDatasource_ShipperID()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT shipperid, companyname FROM Sales.Shippers";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "Sales.Shippers");
            da.Fill(ds, "Sales.Shippers");
            DataTable dt = ds.Tables["Sales.Shippers"];
            da.Fill(dt);
            cbShipperID.DataSource = dt;
            cbShipperID.DisplayMember = "companyname";
            cbShipperID.ValueMember = "shipperid";
        }

        bool ValidateOrders()
        {
            errorProvider1.Clear();
            bool error = false;
            if (dtpRequiredDate.Value <= dtpOrderDate.Value)
            {
                errorProvider1.SetError(dtpOrderDate, "Required date must latter than Order date!");
                errorProvider1.SetError(dtpRequiredDate, "Shipped date must latter than Order date!");
                error = true;
            }
            if (dtpShippedDate.Checked == true)
            {
                if (dtpShippedDate.Value <= dtpOrderDate.Value)
                {
                    errorProvider1.SetError(dtpOrderDate, "Shipped date must latter than Order date!");
                    errorProvider1.SetError(dtpShippedDate, "Shipped date must latter than Order date!");
                    error = true;
                } 
            }
            if (txtShipName.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipName, "Not Allow Null!");
                error = true;
            }
            if (txtShipName.Text.Trim().Length > 40)
            {
                errorProvider1.SetError(txtShipName, "Name is too long!");
                error = true;
            }

            if (nudFreight.Text.Length > 0)
            {
                try
                {
                    decimal tmp = Decimal.Parse(nudFreight.Text.Trim());
                    if (tmp < 0)
                    {
                        errorProvider1.SetError(nudFreight, "Invalid Input!");
                        error = true;
                    }
                }
                catch (Exception)
                {
                    errorProvider1.SetError(nudFreight, "Invalid Input!");
                    error = true;
                }
            }
            else
            {
                nudFreight.Text = "1.0000";
            }

            if (txtShipAddress.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipAddress, "Not Allow Null!");
                error = true;
            }
            if (txtShipAddress.Text.Trim().Length > 60)
            {
                errorProvider1.SetError(txtShipAddress, "Address is too long!");
                error = true;
            }

            if (txtShipCity.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipCity, "Not Allow Null!");
                error = true;
            }
            if (txtShipCity.Text.Trim().Length > 15)
            {
                errorProvider1.SetError(txtShipCity, "Ciy name is too long!");
                error = true;
            }

            if (txtShipRegion.Text.Trim().Length > 15)
            {
                errorProvider1.SetError(txtShipCity, "Ship Region name is too long!");
                error = true;
            }
            if (mtbShipPostalCode.Text.Trim().Length > 0 && mtbShipPostalCode.Text.Trim().Length < 5)
            {
                errorProvider1.SetError(mtbShipPostalCode, "Please complete!");
                error = true;
            }
            if (cbShipCountry.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(cbShipCountry, "Not Allow Null!");
                error = true;
            }
            if (cbShipCountry.Text.Trim().Length > 15)
            {
                errorProvider1.SetError(cbShipCountry, "Country name is too long!");
                error = true;
            }

            if (error == true)
            {
                return false;
            }
            return true;
        }

        #endregion


        #region Event
        private void Orders_Load(object sender, EventArgs e)
        {
            getDataSource_CustomerID();
            getDataSource_EmployeesID();
            getDatasource_ShipperID();
            tmp();
        }
        void tmp()
        {
            cbCustID.SelectedIndex = -1;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (ValidateOrders() == true)
                {
                    AddOrders();
                    gvOrders.Rows.Clear();
                    LoadOrdersInfo();
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Can not Add !", "Error");
            }
        }

        private void gvOrders_SelectionChanged(object sender, EventArgs e)
        {
            
            try
            {
                if (gvOrders.SelectedRows.Count > 0)
                {
                    errorProvider1.Clear();
                    DataGridViewRow r = gvOrders.SelectedRows[0];
                    txtOrderID.Text = r.Cells[0].Value.ToString();
                    string tmp1 = r.Cells[1].Value.ToString().Trim();
                    if (tmp1 != "")
                    {
                        cbCustID.SelectedValue = tmp1;
                    }
                    else
                    {
                        cbCustID.SelectedIndex = -1;
                    }
                    cbEmpID.SelectedValue = r.Cells[2].Value.ToString().Trim();
                    dtpOrderDate.Text = r.Cells[3].Value.ToString().Trim();
                    dtpRequiredDate.Text = r.Cells[4].Value.ToString().Trim();
                    dtpShippedDate.Text = r.Cells[5].Value.ToString().Trim();
                    cbShipperID.SelectedValue = r.Cells[6].Value.ToString().Trim();
                    nudFreight.Text = r.Cells[7].Value.ToString().Trim();
                    txtShipName.Text = r.Cells[8].Value.ToString().Trim();
                    txtShipAddress.Text = r.Cells[9].Value.ToString().Trim();
                    txtShipCity.Text = r.Cells[10].Value.ToString().Trim();
                    txtShipRegion.Text = r.Cells[11].Value.ToString();
                    mtbShipPostalCode.Text = r.Cells[12].Value.ToString().Trim();
                    int tmp = cbShipCountry.FindStringExact(r.Cells[13].Value.ToString(), 0);
                    cbShipCountry.SelectedIndex = tmp;

                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true;
                    btnSeeDetail.Enabled = true;

                    btnAdd.Enabled = false;
                }
            }
            catch (Exception)
            {
                
                MessageBox.Show("Have a fail about value!","Error");
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            initData();
            
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                if (gvOrders.SelectedRows.Count > 0)
                {
                    if (ValidateOrders() == true)
                    {
                        DialogResult r = MessageBox.Show("Are you Sure?", "Confirm?", MessageBoxButtons.YesNo);
                        if (r == DialogResult.Yes)
                        {
                            UpdateOrders();
                            gvOrders.Rows.Clear();
                            LoadOrdersInfo();
                        }
                    }
                }
            }
            catch (Exception)
            {
                
                MessageBox.Show("Can not Update !","Error");
            } 
            
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (gvOrders.SelectedRows.Count > 0)
                {
                    DialogResult r = MessageBox.Show("Are you Sure?", "Confirm?", MessageBoxButtons.YesNo);
                    if (r == DialogResult.Yes)
                    {
                        DeleteOrders();
                        gvOrders.Rows.Clear();
                        LoadOrdersInfo();
                        initData();
                    }
                }
            }
            catch (Exception)
            {
                
                MessageBox.Show("Can not Delete \nHave an other information is using in orther table!","Error");
            }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            try
            {
                gvOrders.Rows.Clear();
                LoadOrdersInfo();
                initData();
                dtpSearch.Value = DateTime.Now;
                cbSearchBy.SelectedIndex = 0;
                btnReload.Enabled = false;
            }
            catch (Exception)
            {
                
                MessageBox.Show("Fail Reload","Error");
            }
            
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                initData();
                btnReload.Enabled = true;
                gvOrders.Rows.Clear();
                SearchOrders();
            }
            catch (Exception)
            {
                
                MessageBox.Show("Connect to Database fail !","Error");
            }
        }

        private void btnSeeDetail_Click(object sender, EventArgs e)
        {
            try
            {
                OrderDetails tblOrderDetails = new OrderDetails(txtOrderID.Text, false);
                tblOrderDetails.Show();
            }
            catch (Exception)
            {

                MessageBox.Show("Call order detail Fail","Error");
            }
        }

        #endregion

     

       




    }
}
